/**
 * Copyright (c) 2009, http://www.softwareinlife.com. All rights reserved.
 * 
 * Redistribution and use in source and binary forms, 
 * with or without modification, are permitted provided that 
 * the following conditions are met:
 * 
 * 1. Redistributions of source code must retain the above copyright notice, 
 *    this list of conditions and the following disclaimer.
 * 
 * 2. Redistributions in binary form must reproduce the above copyright notice, 
 *    this list of conditions and the following disclaimer in the documentation 
 *    and/or other materials provided with the distribution.
 * 
 * 3. Neither the name of the http://www.softwareinlife.com nor the names of its 
 *    contributors may be used to endorse or promote products derived from 
 *    this software without specific prior written permission.
 * 
 * THIS SOFTWARE IS PROVIDED BY THE SOFTWAREINLIFE AND CONTRIBUTORS "AS IS" 
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, 
 * THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 
 * ARE DISCLAIMED. 
 * IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, 
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES 
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; 
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED 
 * AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, 
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE 
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */
package com.softwareinlife.vision.android.data.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.softwareinlife.vision.android.data.exceptions.DatabaseConnectionException;
import com.softwareinlife.vision.android.data.exceptions.EventCreateException;
import com.softwareinlife.vision.android.data.exceptions.EventReadException;
import com.softwareinlife.vision.android.data.exceptions.EventUpdateException;
import com.softwareinlife.vision.android.data.exceptions.VisionCreateException;
import com.softwareinlife.vision.android.data.model.EventModel;
import com.softwareinlife.vision.android.data.model.ScheduleModel;
import com.softwareinlife.vision.android.data.model.UserModel;
import com.softwareinlife.vision.android.system.util.StringUtil;

/**
 * @author Jang, Sun-Jin (jangsunjin@softwareinlife.com)
 * @date 2009. 10. 11.
 */
public class EventDAO extends BaseDAO {

	/**
	 * Constructor
	 * 
	 * @param ctx
	 * @throws DatabaseConnectionException
	 */
	public EventDAO(Context ctx) throws DatabaseConnectionException {
		super(ctx);
	}

	/**
	 * Constructor
	 * 
	 * @param db
	 */
	public EventDAO(SQLiteDatabase db) {
		super(db);
	}

	/**
	 * Create Table
	 * 
	 * @param model
	 * @throws VisionCreateException
	 */
	public void createTable() throws EventCreateException {
		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE TABLE IF NOT EXISTS ");
		sql.append(EventConstants.TABLE_NAME + " ");
		sql.append("(");
		sql.append(EventConstants.COLUMN_OF_IDS
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, ");
		sql.append(UserConstants.COLUMN_OF_IDS + " VARCHAR(40) NOT NULL, ");
		sql.append(ScheduleConstants.COLUMN_OF_IDS + " INTEGER NOT NULL, ");
		sql.append(EventConstants.COLUMN_OF_ACHIEVMENT + " VARCHAR(40) NOT NULL, ");
		sql.append(EventConstants.COLUMN_OF_START_DATE + " VARCHAR(50), ");
		sql.append(EventConstants.COLUMN_OF_END_DATE + " VARCHAR(50), ");
		sql.append(EventConstants.COLUMN_OF_CREATED_DATE
				+ " VARCHAR(50) NOT NULL, ");
		sql.append(EventConstants.COLUMN_OF_MODIFIED_DATE
				+ " VARCHAR(50) NOT NULL, ");
		sql.append(EventConstants.COLUMN_OF_SYNC_DATE + " VARCHAR(50)");
		sql.append(");");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Create Event
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public void create(EventModel model) throws EventCreateException {
		// Check Parameters
		if (model != null
				&& (model.getUser() != null && model.getSchedule() != null)
				&& (model.getAchievement() != null && model.getAchievement()
						.length() > 0)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("INSERT INTO ");
			sql.append(EventConstants.TABLE_NAME + " ");
			sql.append("(");
			sql.append(UserConstants.COLUMN_OF_IDS + ", ");
			sql.append(ScheduleConstants.COLUMN_OF_IDS + ", ");
			sql.append(EventConstants.COLUMN_OF_ACHIEVMENT + ", ");
			sql.append(EventConstants.COLUMN_OF_START_DATE + ", ");
			sql.append(EventConstants.COLUMN_OF_END_DATE + ", ");
			sql.append(EventConstants.COLUMN_OF_CREATED_DATE + ", ");
			sql.append(EventConstants.COLUMN_OF_MODIFIED_DATE);
			sql.append(") ");
			sql.append("VALUES ");
			sql.append("(");
			sql.append("'" + StringUtil.toString(model.getUser().getUserId())
					+ "'" + ", ");
			sql.append("'" + model.getSchedule().getIds() + "'" + ", ");
			sql
					.append("'" + StringUtil.toString(model.getAchievement()) + "'"
							+ ", ");
			sql.append("'" + StringUtil.toString(model.getStartDate()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getEndDate()) + "'"
					+ ", ");
			sql.append("'" + StringUtil.toString(model.getCreatedDate()) + "'"
					+ ", ");
			sql
					.append("'" + StringUtil.toString(model.getModifiedDate())
							+ "'");
			sql.append(");");

			System.out.println(sql.toString());

			db.execSQL(sql.toString());
		}
	}

	/**
	 * Update Event
	 * 
	 * @param model
	 * @throws EventUpdateException
	 */
	public void update(EventModel model) throws EventCreateException {

		// Check Parameters
		if (model != null
				&& (model.getUser() != null && model.getSchedule() != null)
				&& (model.getAchievement() != null && model.getAchievement()
						.length() > 0)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("UPDATE ");
			sql.append(EventConstants.TABLE_NAME + " ");
			sql.append("SET ");
			sql.append(EventConstants.COLUMN_OF_ACHIEVMENT + " = " + "'"
					+ StringUtil.toString(model.getAchievement()) + "'" + " , ");
			sql.append(EventConstants.COLUMN_OF_START_DATE + " = " + "'"
					+ StringUtil.toString(model.getStartDate()) + "'" + ", ");
			sql.append(EventConstants.COLUMN_OF_END_DATE + " = " + "'"
					+ StringUtil.toString(model.getEndDate()) + "'" + ", ");
			sql.append(EventConstants.COLUMN_OF_MODIFIED_DATE + " = " + "'"
					+ StringUtil.toString(model.getModifiedDate()) + "'");
			sql.append(" ");
			sql.append("WHERE ");
			sql.append(EventConstants.COLUMN_OF_IDS + " = " + "'"
					+ model.getIds() + "'");

			System.out.println(sql.toString());
			db.execSQL(sql.toString());
		}
	}

	/**
	 * Update Event
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public void updateSyncDate(Integer ids) throws EventCreateException {

		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE ");
		sql.append(EventConstants.TABLE_NAME + " ");
		sql.append("SET ");
		sql.append(EventConstants.COLUMN_OF_SYNC_DATE + " = " + "'"
				+ StringUtil.toString(new Date()) + "'");
		sql.append(" ");
		sql.append("WHERE ");
		sql.append(EventConstants.COLUMN_OF_IDS + " = " + "'" + ids + "'");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Delete Event
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public void delete(EventModel model) throws EventCreateException {

		// Check Parameters
		if (model != null
				&& (model.getUser() != null && model.getSchedule() != null)
				&& (model.getAchievement() != null && model.getAchievement()
						.length() > 0)) {

			// SQL
			StringBuilder sql = new StringBuilder();
			sql.append("DELETE FROM ");
			sql.append(EventConstants.TABLE_NAME + " ");
			sql.append("WHERE ");
			sql.append(EventConstants.COLUMN_OF_IDS + " = " + "'"
					+ model.getIds() + "'");

			System.out.println(sql.toString());

			db.execSQL(sql.toString());
		}
	}

	/**
	 * Delete Event
	 * 
	 * delete from VS_Event where IDS = 1;
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public void delete(Integer ids) throws EventCreateException {

		// SQL
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM ");
		sql.append(EventConstants.TABLE_NAME + " ");
		sql.append("WHERE ");
		sql.append(EventConstants.COLUMN_OF_IDS + " = " + "'" + ids + "'");

		System.out.println(sql.toString());

		db.execSQL(sql.toString());
	}

	/**
	 * Read Event
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public List<EventModel> readAll() throws EventReadException {

		// Results
		List<EventModel> results = new ArrayList<EventModel>(0);

		// Columns
		String[] columns = new String[] { EventConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				ScheduleConstants.COLUMN_OF_IDS,
				EventConstants.COLUMN_OF_ACHIEVMENT,
				EventConstants.COLUMN_OF_START_DATE,
				EventConstants.COLUMN_OF_END_DATE,
				EventConstants.COLUMN_OF_CREATED_DATE,
				EventConstants.COLUMN_OF_MODIFIED_DATE,
				EventConstants.COLUMN_OF_SYNC_DATE };

		// Query
		Cursor cursor = db.query(EventConstants.TABLE_NAME, columns, null,
				null, null, null, EventConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					EventModel model = new EventModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);

						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}

	public EventModel read(int EventIds) {
		// Results
		EventModel model = null;

		// Columns
		String[] columns = new String[] { EventConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				ScheduleConstants.COLUMN_OF_IDS,
				EventConstants.COLUMN_OF_ACHIEVMENT,
				EventConstants.COLUMN_OF_START_DATE,
				EventConstants.COLUMN_OF_END_DATE,
				EventConstants.COLUMN_OF_CREATED_DATE,
				EventConstants.COLUMN_OF_MODIFIED_DATE,
				EventConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = EventConstants.COLUMN_OF_IDS + " = " + EventIds;

		// Query
		Cursor cursor = db.query(EventConstants.TABLE_NAME, columns,
				selection, null, null, null, EventConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				model = new EventModel();
				for (int i = 0; i < cursor.getColumnCount(); i++) {
					String columnName = cursor.getColumnName(i);
					String columnValue = cursor.getString(i);

					model = putDate(model, columnName, columnValue);
				}
			}
		}

		return model;
	}

	/**
	 * Read Event Sync Data
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public List<EventModel> readSyncData() throws EventReadException {

		// Results
		List<EventModel> results = new ArrayList<EventModel>(0);

		// Add Is Null Results
		List<EventModel> isNull = readSyncDataIsNull();
		results.addAll(isNull);

		// Add Is Chanaged Results
		List<EventModel> isChanged = readSyncDataIsNull();
		results.addAll(isChanged);

		return results;
	}

	/**
	 * Read Event Sync Data is Null
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public List<EventModel> readSyncDataIsNull() throws EventReadException {

		// Results
		List<EventModel> results = new ArrayList<EventModel>(0);

		// Columns
		String[] columns = new String[] { EventConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				ScheduleConstants.COLUMN_OF_IDS,
				EventConstants.COLUMN_OF_ACHIEVMENT,
				EventConstants.COLUMN_OF_START_DATE,
				EventConstants.COLUMN_OF_END_DATE,
				EventConstants.COLUMN_OF_CREATED_DATE,
				EventConstants.COLUMN_OF_MODIFIED_DATE,
				EventConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = "SYNC_DATE is null";

		// Query
		Cursor cursor = db.query(EventConstants.TABLE_NAME, columns,
				selection, null, null, null, EventConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					EventModel model = new EventModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);

						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}

	/**
	 * Read Event Sync Data is Null and Not Syncronized
	 * 
	 * select * from VS_Event where SYNC_DATE is not null and
	 * julianday(MODIFIED_DATE) > julianday(SYNC_DATE);
	 * 
	 * @param model
	 * @throws EventCreateException
	 */
	public List<EventModel> readSyncDataIsChanged() throws EventReadException {

		// Results
		List<EventModel> results = new ArrayList<EventModel>(0);

		// Columns
		String[] columns = new String[] { EventConstants.COLUMN_OF_IDS,
				UserConstants.COLUMN_OF_IDS,
				ScheduleConstants.COLUMN_OF_IDS,
				EventConstants.COLUMN_OF_ACHIEVMENT,
				EventConstants.COLUMN_OF_START_DATE,
				EventConstants.COLUMN_OF_END_DATE,
				EventConstants.COLUMN_OF_CREATED_DATE,
				EventConstants.COLUMN_OF_MODIFIED_DATE,
				EventConstants.COLUMN_OF_SYNC_DATE };

		// Selection
		String selection = "SYNC_DATE is not null and julianday(MODIFIED_DATE) > julianday(SYNC_DATE)";

		// Query
		Cursor cursor = db.query(EventConstants.TABLE_NAME, columns,
				selection, null, null, null, EventConstants.COLUMN_OF_IDS);

		// Data
		if (cursor != null) {
			if (cursor.moveToFirst()) {
				do {
					EventModel model = new EventModel();
					for (int i = 0; i < cursor.getColumnCount(); i++) {
						String columnName = cursor.getColumnName(i);
						String columnValue = cursor.getString(i);

						model = putDate(model, columnName, columnValue);
					}

					results.add(model);
				} while (cursor.moveToNext());
			}
		}

		return results;
	}

	/**
	 * Put Data
	 * 
	 * @param model
	 * @param columnName
	 * @param columnValue
	 */
	public EventModel putDate(EventModel model, String columnName, String columnValue) {
		if (EventConstants.COLUMN_OF_IDS.equals(columnName)) {
			model.setIds(StringUtil.toInt(columnValue));
		} else if (UserConstants.COLUMN_OF_IDS.equals(columnName)) {
			UserDAO dao = new UserDAO(db);
			UserModel user = dao.read(columnValue);
			model.setUser(user);
		} else if (ScheduleConstants.COLUMN_OF_IDS.equals(columnName)) {
			ScheduleDAO dao = new ScheduleDAO(db);
			ScheduleModel schedule = dao.read(StringUtil.toInt(columnValue));
			model.setSchedule(schedule);
		} else if (EventConstants.COLUMN_OF_ACHIEVMENT.equals(columnName)) {
			model.setAchievement(StringUtil.toString(columnValue));
		} else if (EventConstants.COLUMN_OF_START_DATE.equals(columnName)) {
			model.setStartDate(StringUtil.toDate(columnValue));
		} else if (EventConstants.COLUMN_OF_END_DATE.equals(columnName)) {
			model.setEndDate(StringUtil.toDate(columnValue));
		} else if (EventConstants.COLUMN_OF_CREATED_DATE.equals(columnName)) {
			model.setCreatedDate(StringUtil.toDate(columnValue));
		} else if (EventConstants.COLUMN_OF_MODIFIED_DATE.equals(columnName)) {
			model.setModifiedDate(StringUtil.toDate(columnValue));
		} else if (EventConstants.COLUMN_OF_SYNC_DATE.equals(columnName)) {
			model.setSyncDate(StringUtil.toDate(columnValue));
		}
		
		return model;
	}
}
